package com.hkts.naturalgas.dictionaries.utils;

import com.hkts.naturalgas.datahandler.constance.EnviromentConstance;
import com.hkts.naturalgas.datahandler.dataenum.DateEnum;
import com.hkts.naturalgas.datahandler.curvecache.CurveCache;
import com.hkts.naturalgas.dictionaries.entity.HktsEnvironmentProtectionDictionaries;
import com.hkts.naturalgas.dictionaries.entity.HktsEnvironmentProtectionItem;
import com.hkts.naturalgas.dictionaries.service.IHktsEnvironmentProtectionItemService;
import com.hkts.naturalgas.dictionaries.vo.PageVo;
import com.hkts.naturalgas.summary.entity.SummaryDictionary;
import com.hkts.naturalgas.utils.SnowFlakeUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * @ClassNameDBUtils
 * @Description
 * @Author yck
 * @Date2021-06-23 16:24
 * @Version V1.0
 **/
@Component
@Slf4j
public class DBUtils {
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;
    @Autowired
    public IHktsEnvironmentProtectionItemService iHktsEnvironmentProtectionItemService;

    public List<Map<String, Object>> excuteQuerySql(String sql) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        PreparedStatement pst = null;
        SqlSession session = getSqlSession();
        ResultSet result = null;
        try {
            pst = session.getConnection().prepareStatement(sql);
            result = pst.executeQuery();
            ResultSetMetaData md = result.getMetaData(); //获得结果集结构信息,元数据
            int columnCount = md.getColumnCount();   //获得列数
            while (result.next()) {
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), result.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            closeSqlSession(session);
        }
        return list;
    }

    /**
     * 获取sqlSession
     *
     * @return
     */
    public SqlSession getSqlSession() {
        return SqlSessionUtils.getSqlSession(sqlSessionTemplate.getSqlSessionFactory(),
                sqlSessionTemplate.getExecutorType(), sqlSessionTemplate.getPersistenceExceptionTranslator());
    }

    /**
     * 关闭sqlSession
     *
     * @param session
     */
    public void closeSqlSession(SqlSession session) {
        SqlSessionUtils.closeSqlSession(session, sqlSessionTemplate.getSqlSessionFactory());
    }

    /**
     * 功能描述:
     *
     * @return:
     * @Author: 杨春科
     * @Date: 2021-10-15 15:33
     * @Description //TODO  获取最新的一条记录
     */
    public Map<String, Object> getLastRow(String tableName) throws ParseException {
        Map<String, Object> map = CurveCache.lastRowCache.get(tableName);
        if (map != null) {
            return map;
        }
        StringBuffer buffer = new StringBuffer();
        buffer.append("select * from ").append(tableName);
        buffer.append(" order by time desc LIMIT 0,1 ");
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        if (maps.size() > 0) {
            return maps.get(0);
        } else {
            return null;
        }
    }

    public Map<String, Object> getLastRowNoCache(String tableName) throws ParseException {

        StringBuffer buffer = new StringBuffer();
        buffer.append("select * from ").append(tableName);
        buffer.append(" order by time desc LIMIT 0,1 ");
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        if (maps.size() > 0) {
            return maps.get(0);
        } else {
            return null;
        }

    }

    /**
     * 功能描述:
     *
     * @return:
     * @Author: 杨春科
     * @Date: 2021-06-23 16:42
     * @Description //TODO  获取数据
     */
    public List<Map<String, Object>> getList(HktsEnvironmentProtectionDictionaries hktsEnvironmentProtectionDictionaries, List<HktsEnvironmentProtectionItem> hktsEnvironmentProtectionItems, String date, String section) throws ParseException {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select");
        for (int i = 0; i < hktsEnvironmentProtectionItems.size(); i++) {
            buffer.append(" ");
            if (i == 0) {
                buffer.append(hktsEnvironmentProtectionItems.get(i).getFieldName());
            } else {
                buffer.append("," + hktsEnvironmentProtectionItems.get(i).getFieldName());
            }
        }
        buffer.append(" from ").append(hktsEnvironmentProtectionDictionaries.getTableName());
        buffer.append(" where ");
        String[] dateRange = getDateRange(date, section);
        buffer.append("time >='" + dateRange[0] + "' and time <= '" + dateRange[1] + "'");
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }

    public List<Map<String, Object>> getListExport(HktsEnvironmentProtectionDictionaries hktsEnvironmentProtectionDictionaries, List<HktsEnvironmentProtectionItem> hktsEnvironmentProtectionItems, String beiginDate, String endDate) throws ParseException {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select");
        for (int i = 0; i < hktsEnvironmentProtectionItems.size(); i++) {
            buffer.append(" ");
            if (i == 0) {
                buffer.append(hktsEnvironmentProtectionItems.get(i).getFieldName());
            } else {
                buffer.append("," + hktsEnvironmentProtectionItems.get(i).getFieldName());
            }
        }
        buffer.append(" from ").append(hktsEnvironmentProtectionDictionaries.getTableName());
        buffer.append(" where ");
        buffer.append("time >='" + beiginDate + "' and time <= '" + endDate + "'");
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }

    public List<Map<String, Object>> getTenSecondsExport(HktsEnvironmentProtectionDictionaries hktsEnvironmentProtectionDictionaries, List<HktsEnvironmentProtectionItem> hktsEnvironmentProtectionItems,String beiginDate, String endDate, Integer type) throws ParseException {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select");
        for (int i = 0; i < hktsEnvironmentProtectionItems.size(); i++) {
            buffer.append(" ");
            if (i == 0) {
                buffer.append(hktsEnvironmentProtectionItems.get(i).getFieldName());
            } else {
                buffer.append("," + hktsEnvironmentProtectionItems.get(i).getFieldName());
            }
        }
        buffer.append(" from ").append(hktsEnvironmentProtectionDictionaries.getTableName());
        buffer.append(" where ");
        buffer.append("time >='" + beiginDate + "' and time <= '" + endDate + "'");
        if (type == 1){
            buffer.append(" and DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') like '%00'");
        }else if(type == 2) {
            buffer.append(" and DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') like '%0:00'");
        }
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }

    public List<Map<String, Object>> getWdAndGlList(HktsEnvironmentProtectionDictionaries hktsEnvironmentProtectionDictionaries, List<HktsEnvironmentProtectionItem> hktsEnvironmentProtectionItems) throws ParseException {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select");
        for (int i = 0; i < hktsEnvironmentProtectionItems.size(); i++) {
            buffer.append(" ");
            if (i == 0) {
                buffer.append(hktsEnvironmentProtectionItems.get(i).getFieldName());
            } else {
                buffer.append("," + hktsEnvironmentProtectionItems.get(i).getFieldName());
            }
        }
        buffer.append(" from ").append(hktsEnvironmentProtectionDictionaries.getTableName());
        buffer.append(" where ");
        buffer.append("time ORDER BY time DESC LIMIT 1");
        System.out.println(buffer.toString());
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }

    /**
     * 功能描述:
     * 获取当前表条件的数据行数
     *
     * @return:
     * @Author: 杨春科
     * @Date: 2021-10-08 16:51
     * @Description //TODO
     */
    public Integer getCount(PageVo vo, String tableName) throws ParseException {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select count(*) count from ").append(tableName);
        buffer.append(" where ");
        buffer.append("time >='").append(vo.getStartTime()).append("' ");
        if (StringUtils.isNotBlank(vo.getEndTime())) {
            buffer.append("and time <= '").append(vo.getEndTime()).append("' ");
        }
        if (StringUtils.isNotBlank(vo.getTimeInterval())) {
            buffer.append(" and time like '").append(vo.getTimeInterval()).append("'");
        }
        log.info("count sql:{}", buffer.toString());
        List<Map<String, Object>> list = excuteQuerySql(buffer.toString());
        int count = Integer.parseInt(list.get(0).get("count").toString());
        return count;
    }

    //获取结果集1
    public List<Map<String, Object>> getPageResult(PageVo vo, String tableName) throws ParseException {
        List<HktsEnvironmentProtectionItem> tableItemByTableId = iHktsEnvironmentProtectionItemService.getTableItemByTableId(vo.getTableId());
        StringBuffer buffer = new StringBuffer();
        buffer.append("select ");
        String orderby = "";
        for (int i = 0; i < tableItemByTableId.size(); i++) {
            if (tableItemByTableId.get(i).getDataType().equals("datetime")) {
                orderby = tableItemByTableId.get(i).getFieldName();
                buffer.append("DATE_FORMAT(").append(tableItemByTableId.get(i).getFieldName()).append(",'%Y-%m-%d %H:%i:%s') as ").append(tableItemByTableId.get(i).getFieldName()).append(",");
            } else {
                buffer.append(tableItemByTableId.get(i).getFieldName()).append(",");
            }
        }
        buffer.delete(buffer.length() - 1, buffer.length());
        buffer.append(" from ").append(tableName);
        buffer.append(" where ");
        buffer.append("time >=' ").append(vo.getStartTime()).append("' ");
        if (StringUtils.isNotBlank(vo.getEndTime())) {
            buffer.append("and time <= '").append(vo.getEndTime()).append("' ");
        }
        if (StringUtils.isNotBlank(orderby)) {
            buffer.append(" order by ").append(orderby);
        }
        buffer.append(" limit ").append(vo.getStartRow()).append(",").append(vo.getPageSize());
        log.info("result sql:{}", buffer.toString());
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }

    public List<Map<String, Object>> getPageResult(PageVo vo, String tableName, List<HktsEnvironmentProtectionItem> tableItemByTableId) throws ParseException {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select ");
        String orderby = "";
        for (int i = 0; i < tableItemByTableId.size(); i++) {
            if (tableItemByTableId.get(i).getDataType().equals("datetime")) {
                orderby = tableItemByTableId.get(i).getFieldName();
                buffer.append("DATE_FORMAT(").append(tableItemByTableId.get(i).getFieldName()).append(",'%Y-%m-%d %H:%i:%s') as ").append(tableItemByTableId.get(i).getFieldName()).append(",");
            } else {
                buffer.append(tableItemByTableId.get(i).getFieldName()).append(",");
            }
        }
        buffer.delete(buffer.length() - 1, buffer.length());
        buffer.append(" from ").append(tableName);
        buffer.append(" where ");
        buffer.append("time >=' ").append(vo.getStartTime()).append("' ");
        if (StringUtils.isNotBlank(vo.getEndTime())) {
            buffer.append("and time <= '").append(vo.getEndTime()).append("' ");
        }
        if (StringUtils.isNotBlank(vo.getTimeInterval())) {
            buffer.append(" and time like '").append(vo.getTimeInterval()).append("'");
        }

        if (StringUtils.isNotBlank(orderby)) {
            buffer.append(" order by ").append(orderby).append(" asc");
        }
        buffer.append(" limit ").append(vo.getStartRow()).append(",").append(vo.getPageSize());
        log.info("result sql:{}", buffer.toString());
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }


    /**
     * 功能描述:
     *
     * @return:
     * @Author: 杨春科
     * @Date: 2021-06-23 17:11
     * @Description //TODO 计算开始时间 结束时间
     */
    public String[] getDateRange(String date, String section) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int startTime = 0;
        int endTime = 0;
        if (section.equals("1")) {
            startTime = 480;
            endTime = 1200;
        } else {
            startTime = 1200;
            endTime = 1680;
        }
        Date parse = format.parse(date);
        Calendar c = new GregorianCalendar();
        c.setTime(parse);
        c.add(Calendar.MINUTE, startTime);
        Date time = c.getTime();
        String format1 = format2.format(time);
        Calendar c2 = new GregorianCalendar();
        c2.setTime(parse);
        c2.add(Calendar.MINUTE, endTime);
        Date time2 = c2.getTime();
        String format3 = format2.format(time2);
        String[] arr = {format1, format3};
        return arr;
    }

    //sql拼接
    public String updateSql(String tableName, List<HktsEnvironmentProtectionItem> tableItemByTableId, Map params, String id) {
        SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        StringBuffer buffer = new StringBuffer();
        StringBuffer valuesbuffer = new StringBuffer();
        buffer.append("insert into ").append(tableName);
        buffer.append(" ( id,");
        valuesbuffer.append("values ('").append(id).append("',");
        for (int i = 0; i < tableItemByTableId.size(); i++) {
            if (tableItemByTableId.get(i).getDataType().equals(EnviromentConstance.DATE_TIME)) {
                buffer.append(tableItemByTableId.get(i).getFieldName()).append(",");
                valuesbuffer.append("'").append(sim.format(new Date())).append("',");
            } else {
                buffer.append(tableItemByTableId.get(i).getFieldName()).append(",");
                valuesbuffer.append(params.get(tableItemByTableId.get(i).getFieldName())).append(",");
            }
        }
        buffer.delete(buffer.length() - 1, buffer.length());
        valuesbuffer.delete(valuesbuffer.length() - 1, valuesbuffer.length());
        buffer.append(") ");
        valuesbuffer.append(")");
        buffer.append(valuesbuffer);
        return buffer.toString();
    }

    //批量插入处理
    public  void updateBatch(List<String> list) {
        //log.info("sql集合：{}",list);
        Statement pst = null;
        SqlSession session = getSqlSession();
        try {
            pst = session.getConnection().createStatement();
            for (int i = 0; i < list.size(); i++) {
                pst.addBatch(list.get(i));
            }
            int[] ints = pst.executeBatch();
            log.info("批量插入条数：{}", ints.length);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            closeSqlSession(session);
        }

    }

    //数据保存
    public void saveData(String tableName, List<HktsEnvironmentProtectionItem> tableItemByTableId, Map params) {
        SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String id = String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId());
        StringBuffer buffer = new StringBuffer();
        StringBuffer valuesbuffer = new StringBuffer();
        buffer.append("insert into ").append(tableName);
        buffer.append(" ( id,");
        valuesbuffer.append("values ('").append(id).append("',");
        for (int i = 0; i < tableItemByTableId.size(); i++) {
            if (tableItemByTableId.get(i).getDataType().equals(EnviromentConstance.DATE_TIME)) {
                buffer.append(tableItemByTableId.get(i).getFieldName()).append(",");
                valuesbuffer.append("'").append(sim.format(new Date())).append("',");
            } else {
                buffer.append(tableItemByTableId.get(i).getFieldName()).append(",");
                valuesbuffer.append(params.get(tableItemByTableId.get(i).getFieldName())).append(",");
            }
        }
        buffer.delete(buffer.length() - 1, buffer.length());
        valuesbuffer.delete(valuesbuffer.length() - 1, valuesbuffer.length());
        buffer.append(") ");
        valuesbuffer.append(")");
        buffer.append(valuesbuffer);
        log.info("插入数据库sql:{}", buffer.toString());
        PreparedStatement pst = null;
        SqlSession session = getSqlSession();
        try {
            pst = session.getConnection().prepareStatement(buffer.toString());
            pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            closeSqlSession(session);
        }
    }

    //数据删除处理
    public void deleteData(String sql) {
        log.info("删除数据sql:{}", sql);
        PreparedStatement pst = null;
        SqlSession session = getSqlSession();
        try {
            pst = session.getConnection().prepareStatement(sql);
            pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            closeSqlSession(session);
        }
    }

    public List<String> deleteSql(String tableName, List<String> dateList) {
        List<String> sqlList = new ArrayList<String>();
        StringBuffer buffer = new StringBuffer();
        for (int i = 0; i < dateList.size(); i++) {
            buffer.delete(0, buffer.length());
            buffer.append("delete from ").append(tableName).append(" where time<'").append(dateList.get(i)).append("'");
            if (i != dateList.size() - 1) {
                buffer.append(" and ").append(" DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') not like '").append(DateEnum.getName(i)).append("'");
            }
            sqlList.add(buffer.toString());
        }
        return sqlList;
    }
    public  String getSummaryInsertSql(SummaryDictionary summaryDictionary, String time, String s02, String nox,String wasteGas,String smoke){
        StringBuffer buffer=new StringBuffer();
        buffer.append("insert into ")
                .append(summaryDictionary.getSummaryTable())
                .append("(time,so2,nox,waste_gas,smoke) ")
                .append(" values (");
        buffer.append(time).append(",")
                .append(s02).append(",")
                .append(nox).append(",")
                .append(wasteGas).append(",")
                .append(smoke).append(")");
        return buffer.toString();
    }

    public  int getSummaryCountByTime(SummaryDictionary summaryDictionary, String time){
        StringBuffer buffer=new StringBuffer();
        buffer.append("select * from ")
                .append(summaryDictionary.getSummaryTable())
                .append(" where time= '")
                .append(time).append("'");
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps.size();
    }

    public List<Map<String, Object>> getSummaryByTableAndDate(String tableName,String date){
        StringBuffer buffer=new StringBuffer();
        buffer.append("select sum(so2) so2,sum(nox) nox,sum(waste_gas) wasteGas,sum(smoke) smoke, date_format(time, '%H' ) time from ");
        buffer.append(tableName);
        buffer.append( " where time like '" );
        buffer.append(date).append("%'");
        buffer.append("GROUP BY  date_format(time, '%H' )");
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }

    public List<Map<String, Object>> getDaySummaryByTableAndDate(String tableName,String date){
        StringBuffer buffer=new StringBuffer();
        buffer.append("select sum(so2) so2,sum(nox) nox,sum(waste_gas) wasteGas,sum(smoke) smoke from ");
        buffer.append(tableName);
        buffer.append( " where time like '" );
        buffer.append(date).append("%'");
        log.info(buffer.toString());
        List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
        return maps;
    }
}
